# -*- coding: utf-8 -*-
from crm_data_report.config import crm_cal_fields

__author__ = 'sunnychou'
__date__ = '2019/6/19 15:25'

'''
product manager 产品经理数据库处理过程
'''
from common.mssql_helper import g_msql_inst

from common.utils import create_uuid,format_float

from  collections import defaultdict

#ProductMgrDbo封装操作产品经理相关数据库操作
class ProductMgrDbo(object):

    @staticmethod
    def get_global_id_name():
        '''
        获取id和名字的对比字典
        :param sql: 封装的sql语句
        :return:
        '''
        sql = "select DISTINCT ID,	sName  from  org_employee"
        res_list = g_msql_inst.fetch_all(sql)
        # print(res_list)
        # return res_list
        # select  khqc, idowner from  account
        # 假设按照idowner进行group by
        name_dict = defaultdict(dict)
        for (ID, sName) in res_list:
            name_dict[ID] = sName
        return name_dict

    @staticmethod
    def get_product_score_by_person(sql):
        '''
        获取产品经理的产品积分
        :param sql: 封装的sql语句
        :return:
        '''
        res_list = g_msql_inst.fetch_all(sql)
        #print(res_list)
        if len(res_list[0]) == 0 or res_list[0][0] == None:
            return 0
        score = float(res_list[0][0]) if len(res_list) != 0 else 0
        return score

    @staticmethod
    def get_pm_score_list(sql, name_dic):
        '''
        获取销售经理绩效
        :param sql: 封装的sql语句
        :return:
        '''
        res_list = g_msql_inst.fetch_all(sql)

        # 假设按照idowner进行group by
        pm_score_dict = defaultdict(list)
        for (pm_score, owner) in res_list:
            idowner = name_dic.get(owner)
            pm_score_dict[idowner].append((format_float(pm_score)))
        return pm_score_dict


    @staticmethod
    def store_cpjlrckh_statics(endtime, report_list, begintime):
        '''
        保存数据库信息
        :param report_list: 统计出的报表结果数据cpjlrckh
        :return:
        '''

        db_insert_sql = crm_cal_fields['product_rp']['db_insert_sql']
        value_tuple_list = []
        timelist = endtime.split("-")
        khmonth = f"{timelist[0]}-{timelist[1]}"
        for  db_report_dict in report_list:
            id = create_uuid()
            value_tuple = (id, db_report_dict['sname'], endtime, db_report_dict['cpscore'], db_report_dict['cprate'], db_report_dict['xmscore'],
                           db_report_dict['xmrate'], db_report_dict['glgzsore'], db_report_dict['glgzrate'], db_report_dict['mtscore'],
                           db_report_dict['avscore'], db_report_dict['jxgzpd'], db_report_dict['mtxishu'], begintime, khmonth)
            excuete_sql = db_insert_sql % value_tuple
            print(excuete_sql)
            g_msql_inst.db_excuete(excuete_sql)
            value_tuple_list.append(value_tuple)
        print("store_cpjlrckh_statics all finish.")


